<?php
/**
 * 数据库架构处理类
 *
 * @author Mo yi <root@imoi.cn>
 * @link   http://www.imoi.cn
 * @copyright Copyright &copy; 2010-2012 DuoLam Software LLC
 * @license http://www.imoi.cn/license/
 * @version $Id: dpSchema.php 2799 2012-04-10 09:31:13Z Mo.yi $
 * @package db.schema
 * @since 1.0
 */

class dpSchema extends dpComponent
{
	/**
	 * 数据库连接
	 */
	protected $connect;

	/**
	 * dpDbCommand
	 */
	protected $cmd;

	/**
	 * 构造方法，获取当前数据库句柄
	 * @param object 数据库句柄
	 * @param object dpDbCommand 句柄
	 */
	public function __construct(&$connect)
	{
		$this->connect = $connect;
		//$this->cmd     = $cmd;
		unset($connect);
	}

	/**
	 * 获取SELECT部分语句
	 * @param string or array 字段列表，默认为所有
	 * @return string  SELECT部分的SQL语句
	 */
	public function getSelectString($columns = '*', $distinct = false)
	{
		if (is_array($columns))
			$fields = $this->setAlias($columns);
		else {
			if ($columns == '*')
				$fields = '*';
			else
				$fields = $columns;
		}
		return $distinct ? "SELECT DISTINCT " . $fields : "SELECT " . $fields;
	}

	/**
	 * 获取FROM部分语句
	 * @param string or array $table 要查询的表列表
	 * @reteurn string FROM部分的SQL语句
	 */
	public function getFromString($table)
	{
		if (is_array($table))
			$tables = $this->setAlias($table);
		else
			$tables = $this->quotesName($table);

		return " FROM " . $tables;
	}

	/**
	 * 获取JOIN部分语句
	 * @param string or array $table 表名称
	 * @param string $type           连接类型 inner|left|right|full
	 * @param string or array $conditons 连接查询条件
	 * @return string JOIN部分的SQL语句
	 */
	public function getJoinString($table,$type,$conditions)
	{
		$join = $this->getJoinType($type);
		$conditions = $this->parseConditions($conditions);

		if (is_array($table))
			$tables = $this->setAlias($table);
		else
			$tables = $this->quoteSimpleName($table);

		return $join . $tables . ' ON ' . (empty($conditions) ? '1=1' : $conditions);
	}

	/**
	 * 获取WHERE部分语句
	 * @param string or array conditions 查询条件
	 * @return string WHERE部分的SQL语句
	 */
	public function getWhereString($conditions)
	{
		$conditions = $this->parseConditions($conditions);
		return ' WHERE ' . (empty($conditions) ? '1=1' : $conditions);
	}

	/**
	 * 获取分组部分语句
	 * @param string or array $columns 字段名称
	 * @return string 分组部分的SQL语句
	 */
	public function getGroupString($columns)
	{
		if (empty($columns)) return null;

		$string = ' GROUP BY ';

		if (is_array($columns))
			return $string . implode(', ',$columns);
		else
			return $string . $columns;
	}

	/**
	 * 获取分组后的HAVING部分语句
	 * @param string or array $conditions 条件
	 * @return string HAVING部分的SQL语句
	 */
	public function getHavingString($conditions)
	{
		if (empty($conditions)) return null;

		$conditions = $this->parseConditions($conditions);
		return ' HAVING ' . (empty($conditions) ? '1=1' : $conditions);
	}

	/**
	 * 获取ORDER部分语句
	 * @param string or array $columns 字段列表
	 * @return string ORDER部分的SQL语句
	 */
	public function getOrderString($columns)
	{
		if (empty($columns)) return null;

		$string = ' ORDER BY ';

		if (is_array($columns))
			return $string . implode(', ', $columns);
		else
			return $string . $columns;
	}

	/**
	 * 获取LIMIT部分语句
	 * @param int $limit  起始行数位置
	 * @param int $offset 限制行数
	 * @return string LIMIT部分的SQL语句
	 */
	public function getLimitString($limit, $offset = '')
	{
		if (!is_int($limit) && empty($limit)) return null;

		if (empty($offset))
			return ' LIMIT ' . $limit;
		else
			return ' LIMIT ' . $limit . ' , ' . $offset;
	}

	/**
	 * 解析SQL查询条件
	 * 参数为字符型的时候，则直接返回
	 * 如果为数组，第一个值必须是OR|AND|IN|NOT IN|LIKE|NOT LIKE...等等
	 * 第二个值为数组或者字段名或者查询条件，根据第一个值而改变
	 * 如果第一个值为OR或者AND的时候，下面还有数组，将进行递归处理。
	 * 返回结果为字符串型的SQL语句
	 * @param string or array $conditions 查询条件
	 */
	public function parseConditions($conditions)
	{
		if (!is_array($conditions))
			return $conditions;
		else if ($conditions === array())
			return '';

		$count = count($conditions);
		$type  = strtoupper($conditions[0]);

		if ($type === 'OR' or $type === 'AND') {
			$params = array();
			for ($i = 1; $i < $count; ++$i) {
				$condition = $this->parseConditions($conditions[$i]);
				if($condition !== '') $params[] = "(" . $condition . ")";
			}
			return $params === array() ? '' : implode(' ' .$type. ' ', $params);
		}

		if (!isset($conditions[1],$conditions[2]))
			return '';
		
		$params = array();
		$column = $conditions[1];
		$values = $conditions[2];

		if ($type === 'IN' or $type === 'NOT IN') {
			if (empty($values))
				return $type === 'IN' ? '0=1' : '';
			else if (is_array($values)) {
				foreach ($values as $v)
					$params[] = $this->connect->escape($v);
			}

			return $this->quoteSimpleName($column) . ' ' . $type . 
				   ' (' . implode(', ', $params) . ')';
		}

		if (
			$type === 'LIKE' or 
			$type === 'NOT LIKE' or 
			$type === 'OR LIKE' or 
			$type === 'OR NOT LIKE'
		  )
		{
			if (empty($values))
				return $type==='LIKE' or $type==='OR LIKE' ? '0=1' : '';

			if ($type==='LIKE' or $type==='NOT LIKE')
				$andor=' AND ';
			else {
				$andor=' OR ';
				$type = $type === 'OR LIKE' ? 'LIKE' : 'NOT LIKE';
			}

			if (is_array($values)) {

				foreach ($values as $val)
					$params[] = $this->quoteSimpleName($column) .' '. $type . ' ' . $this->connect->escape($val);

				return implode($andor,$params);
			} else
				return $this->quoteSimpleName($column) . ' ' . $type . ' ' .
					   $this->connect->escape($values);
		}
		dpException::error('条件参数不正确，dpSchema');
	}

	public function getInsertString($table, $column)
	{
		$param = $this->_parseParam($column);
		$name  = $value = null;

		foreach ($param['name'] as $k => $v) {
			$name  .= $this->quoteSimpleName($v) . ', ';
			$value .= $this->connect->escape($param['value'][$k]) . ', ';
		}

		return 'INSERT INTO '. $this->quoteSimpleName($table) 
				.'(' . substr($name,0,-2) . ') '
				. ' VALUES(' . substr($value,0,-2) . ')';
	}

	public function getUpdateString($table, $column, $conditions)
	{
		$param  = $this->_parseParam($column);
		$params = null;

		foreach ($param['name'] as $k => $v) {
			$params .= $this->quoteSimpleName($v) . '=' . $this->connect->escape($param['value'][$k]) .', ';
		}

		return 'UPDATE ' . $this->quoteSimpleName($table)
				. ' SET ' . substr($params, 0, -2)
				. ' WHERE ' . $this->parseConditions($conditions);
	}

	public function getDelteString($table, $conditions)
	{
		return 'DELETE FROM ' . $this->quoteSimpleName($table)
				. ' WHERE ' . $this->parseConditions($conditions);
	}

	/**
	 * 设置表和字段的别名
	 * 对表或者字段设置AS语句
	 * @param array $name 表或者字段数组
	 * @return string 带AS的SQL语句
	 */
	protected function setAlias($name)
	{
		$names = null;
		foreach ($name as $n => $alias)
			$names .= $n .' AS ' . $this->quoteSimpleName($alias) .', ';
		return substr($names,0,-2);
	}

	/**
	 * 获取经过处理的表名或字段名
	 * 对表名或者字段名加  `  符号
	 * @param array $name 表或者字段数组
	 * @return string 处理后的SQL语句
	 */
	protected function quotesName($name)
	{
		$name   = explode(',',$name);
		$string = null;
		foreach ($name as $v)
			$string .= $this->quoteSimpleName($v).', ';
		return substr($string,0,-2);
	}

	protected function quoteSimpleName($name)
	{
		return '`' . $name . '`';
	}

	/**
	 * 获取JOIN类型
	 * @param string $type 类型
	 * @return JOIN类型的SQL语句
	 */
	protected function getJoinType($type)
	{
		static $joinType = array
		(
			'inner' => ' INNER JOIN ',
			'left'  => ' LEFT JOIN ',
			'right' => ' RIGHT JOIN ',
			'full'  => ' FULL JOIN ',
			'outer' => ' LEFT OUTER JOIN ',
		);
		return isset($joinType[$type]) ? $joinType[$type] : ' LEFT JOIN ';
	}

	/**
	 * 以下是对数据库架构的操作
	 */

	/**
	 * 获取系统内置字段类型
	 * 如果不是内置类型，返回自定义类型
	 */
	public function getColumnType($type)
	{
		if (isset($this->columnType[$type]))
			return $this->columnType[$type];
		else
			return $type;
	}

	/**
	 * 获取创建数据表的SQL语句
	 * @param string $tableName 表名称
	 * @param array  $columns   表字段
	 * @param string $options   表扩展设置
	 * @param string SQL语句字符串
	 */
	public function getCreateTableString($tableName,$columns,$options=null)
	{
		foreach ($columns as $name => $type) {
			$t = $this->getColumnType($type) ? $this->getColumnType($type) : $type;
			$cols[] = "\t" . $this->quoteSimpleName($name) . ' ' . $t; 
		}

		$sql = 'CREATE TABLE '. $this->quoteSimpleName($tableName) .
			   " (\n".implode(",\n",$cols)."\n)";
		return $options == null ? $sql : $sql . ' ' . $options;
	}

	/**
	 * 获取删除数据表的SQL语句
	 * @param string $tableName 表名称
	 * @return string 返回删除数据表的SQL语句
	 */
	public function getDropTableString($tableName)
	{
		$string = 'DROP TABLE IF EXISTS ';

		if (is_array($tableName))
			$string .= implode(', ',$tableName);
		else
			$string .= $this->quoteSimpleName($tableName);

		return $string;
	}

	/**
	 * 获取增加字段的SQL语句
	 * @param string $tableName 表名称
	 * @param string $name      新的字段名称
	 * @param string $type      字段数据类型，可以使用内置， 也可以自定义
	 * @return string 返回增加字段的SQL语句
	 */
	public function getAddColumnString($table, $name, $type)
	{
		return 'ALTER TABLE ' . $this->quoteSimpleName($table)
			. ' ADD ' . $this->quoteSimpleName($name) . ' '
			. $this->getColumnType($type);
	}

	/**
	 * 获取删除字段的SQL语句
	 * @param string $tableName 表名称
	 * @param string $name      字段名称
	 * @return string 返回删除字段的SQL语句
	 */
	public function getDropColumnString($table, $name)
	{
		return 'ALTER TABLE ' . $this->quoteSimpleName($table) . ' DROP COLUMN ' . $this->quoteSimpleName($name);
	}

	/**
	 * 获取重命名字段的SQL语句
	 * @param string $tableName 表名称
	 * @param string $name      老字段名称
	 * @param string $newName 	新字段名称
	 * @return string 返回重命名字段的SQL语句
	 */
	public function getRenameColumnString($table, $name, $newName)
	{
		return 'ALTER TABLE ' . $this->quoteSimpleName($table)
			. ' RENAME COLUMN ' . $this->quoteSimpleName($name)
			. ' TO ' . $this->quoteSimpleName($newName);
	}

	/**
	 * 获取修改字段的SQL语句
	 * @param string $tableName 表名称
	 * @param string $name      字段名称
	 * @param string $type   	字段类型
	 * @return string 返回修改字段的SQL语句
	 */
	public function getAlertColumnString($table, $name, $type)
	{
		return 'ALTER TABLE ' . $this->quoteSimpleName($table) . ' CHANGE '
			. $this->quoteSimpleName($name) . ' '
			. $this->quoteSimpleName($name) . ' '
			. $this->getColumnType($type);
	}

	/**
	 * 解析数组参数
	 * @param array $array 数组
	 * @return array 返回解析好的参数数组
	 */
	protected function _parseParam($array)
	{
		if (is_array($array)) {
			$param = array();

			foreach ($array as $k => $v) {
				$param['name'][]  = $k;
				$param['value'][] = $this->connect->escape($v);
			}

			return $param;
		} else
			dpException::error('给予的SQL语句参数不正确，dpSchema');
	}
}
?>